cd process_raw_data

source SQLite_utilities.sh

### Define constants
DB_SCOPE=../data/raw_data/z_scope.db
DB_CENSUS=../data/raw_data/z_census.db
DB_TOURISM=../data/raw_data/z_tourism.db
DB_ELECTIONS=../data/raw_data/z_elections.db
DB_PUBLIC_TRANSIT=../data/raw_data/z_publicTransit.db
DB_RENTAL_AGENCIES=../data/raw_data/z_rentalAgencies.db
DB_RAIL_STRIKES=../data/raw_data/z_railStrikes.db
DB_HOLIDAYS=../data/raw_data/z_holidays.db
DB_GTRENDS=../data/raw_data/z_GoogleTrends.db
DB_CAR_COSTS=../data/raw_data/z_carCosts.db
DB_WEATHER=../data/raw_data/z_weather.db

DB_TMP=../data/raw_data/z_tmp.db

OUTPUT_FOLDER=../data/data_csv
mkdir -p $OUTPUT_FOLDER

# Copy platform data
mkdir -p $OUTPUT_FOLDER/platformData
cp ../data/raw_data/platformData/evaluationsData.csv $OUTPUT_FOLDER/platformData/evaluationsData.csv
cp ../data/raw_data/platformData/providerData.csv ../data/data_csv/platformData/providerData.csv
cp ../data/raw_data/platformData/userData.csv ../data/data_csv/platformData/userData.csv

# Process geography and time + define scope
source prepare_geography_and_time.sh

# Census
copyTable2otherDB $DB_SCOPE $DB_CENSUS all_communes
copyTable2otherDB $DB_SCOPE $DB_CENSUS mycommunes
copyTable2otherDB $DB_SCOPE $DB_CENSUS Scope_CantonId
copyTable2otherDB $DB_SCOPE $DB_CENSUS Area_CantonId
source processData_census.sh

# Tourism lodging capacity and tourist flows
copyTable2otherDB $DB_SCOPE $DB_TOURISM mycommunes
copyTable2otherDB $DB_SCOPE $DB_TOURISM Scope_CantonId
source processData_tourism.sh

# Election results
copyTable2otherDB $DB_SCOPE $DB_ELECTIONS all_communes
copyTable2otherDB $DB_SCOPE $DB_ELECTIONS mycommunes
copyTable2otherDB $DB_SCOPE $DB_ELECTIONS Scope_CantonId
source processData_elections.sh

# Public transit stops
copyTable2otherDB $DB_SCOPE $DB_PUBLIC_TRANSIT mycommunes
copyTable2otherDB $DB_SCOPE $DB_PUBLIC_TRANSIT Scope_CantonId
source processData_publicTransitStops.sh

# Car rental agencies
copyTable2otherDB $DB_SCOPE $DB_RENTAL_AGENCIES mycommunes
copyTable2otherDB $DB_SCOPE $DB_RENTAL_AGENCIES Scope_CantonId
source processData_carRentalAgencies.sh

# Rail strikes
copyTable2otherDB $DB_SCOPE $DB_RAIL_STRIKES mydays
copyTable2otherDB $DB_SCOPE $DB_RAIL_STRIKES Scope_day
copyTable2otherDB $DB_SCOPE $DB_RAIL_STRIKES Scope_week
source processData_railStrikes.sh

# Holidays and school vacations
copyTable2otherDB $DB_SCOPE $DB_HOLIDAYS mydays
copyTable2otherDB $DB_SCOPE $DB_HOLIDAYS Scope_day
copyTable2otherDB $DB_SCOPE $DB_HOLIDAYS Scope_week
copyTable2otherDB $DB_SCOPE $DB_HOLIDAYS Scope_CantonId_day
copyTable2otherDB $DB_SCOPE $DB_HOLIDAYS Scope_CantonId_week
source processData_holidays.sh

# Google Trends
copyTable2otherDB $DB_SCOPE $DB_GTRENDS mydays
copyTable2otherDB $DB_SCOPE $DB_GTRENDS myweeks
copyTable2otherDB $DB_SCOPE $DB_GTRENDS Scope_CantonId_day
copyTable2otherDB $DB_SCOPE $DB_GTRENDS Scope_CantonId_week
source processData_GoogleTrends.sh

# Car costs
copyTable2otherDB $DB_SCOPE $DB_CAR_COSTS mycommunes
copyTable2otherDB $DB_SCOPE $DB_CAR_COSTS mydays
copyTable2otherDB $DB_SCOPE $DB_CAR_COSTS myweeks
copyTable2otherDB $DB_SCOPE $DB_CAR_COSTS Scope_CantonId_day
copyTable2otherDB $DB_SCOPE $DB_CAR_COSTS Scope_CantonId_week
source processData_carCosts.sh

# Weather
copyTable2otherDB $DB_SCOPE $DB_WEATHER mydays
copyTable2otherDB $DB_SCOPE $DB_WEATHER Scope_CantonId_day
copyTable2otherDB $DB_SCOPE $DB_WEATHER Scope_CantonId_week
source processData_weather.sh


########### PUT EVERYTHING TOGETHER ###########
# data2: variables that vary at canton level
copyTable2otherDB $DB_SCOPE           $DB_TMP Scope_CantonId
copyTable2otherDB $DB_SCOPE           $DB_TMP Area_CantonId
copyTable2otherDB $DB_CENSUS          $DB_TMP Census_CantonId
copyTable2otherDB $DB_CENSUS          $DB_TMP Commuting_CantonId
copyTable2otherDB $DB_CENSUS          $DB_TMP Diplomas_CantonId
copyTable2otherDB $DB_CENSUS          $DB_TMP Income_CantonId
copyTable2otherDB $DB_ELECTIONS       $DB_TMP ElectionResults_CantonId
copyTable2otherDB $DB_PUBLIC_TRANSIT  $DB_TMP PublicTransitStops_CantonId
copyTable2otherDB $DB_RENTAL_AGENCIES $DB_TMP CarRentalAgencies_CantonId
copyTable2otherDB $DB_TOURISM         $DB_TMP TouristLodgingCapacity_CantonId

leftJoin $DB_TMP 1 a1 Scope_CantonId Area_CantonId                    CantonId
leftJoin $DB_TMP 1 a2  a1            Census_CantonId                  CantonId
leftJoin $DB_TMP 1 a3  a2            Commuting_CantonId               CantonId
leftJoin $DB_TMP 1 a4  a3            Diplomas_CantonId                CantonId
leftJoin $DB_TMP 1 a5  a4            Income_CantonId                  CantonId
leftJoin $DB_TMP 1 a6  a5            ElectionResults_CantonId         CantonId
leftJoin $DB_TMP 1 a7  a6            PublicTransitStops_CantonId      CantonId
leftJoin $DB_TMP 1 a8  a7            CarRentalAgencies_CantonId       CantonId
leftJoin $DB_TMP 1 a9  a8            TouristLodgingCapacity_CantonId  CantonId

selectColumns $DB_TMP 1 a10 a9 CantonId Population NumHHsWithCar NumPersons19orOlder UnemploymentRate PopDensity \
    NumPublicTransitStops NumCarRentalAgencies NumTouristBeds PctageVoix_JOLY MedianIncome Area \
    Fraction_DIPL_01 Fraction_DIPL_02 Fraction_DIPL_03 Fraction_DIPL_11 Fraction_DIPL_12 Fraction_DIPL_13 \
    Fraction_DIPL_14 Fraction_DIPL_15 Fraction_DIPL_16 Fraction_DIPL_17 Fraction_DIPL_18 NumCommutersToLocation

exportCSVFile $DB_TMP a10 $OUTPUT_FOLDER/covariates/data2_CantonId.csv
rm $DB_TMP


# data3: variables that vary at day/week level
copyTable2otherDB $DB_SCOPE        $DB_TMP Scope_day
copyTable2otherDB $DB_SCOPE        $DB_TMP Scope_week
copyTable2otherDB $DB_RAIL_STRIKES $DB_TMP SNCF_day
copyTable2otherDB $DB_RAIL_STRIKES $DB_TMP SNCF_week
copyTable2otherDB $DB_HOLIDAYS     $DB_TMP NationalHolidays_day
copyTable2otherDB $DB_HOLIDAYS     $DB_TMP NationalHolidays_week

leftJoin $DB_TMP 1 a1 Scope_day NationalHolidays_day dayNum
leftJoin $DB_TMP 1 a2  a1       SNCF_day             dayNum
selectColumns $DB_TMP 1 a3 a2 dayNum weekNum dayOfWeek yearMonth month year NumHolidays NumStrikeDays
exportCSVFile $DB_TMP a3 $OUTPUT_FOLDER/covariates/data3_day.csv
dropTables $DB_TMP a1 a2 a3

leftJoin $DB_TMP 1 a1 Scope_week NationalHolidays_week weekNum
leftJoin $DB_TMP 1 a2  a1       SNCF_week             weekNum
selectColumns $DB_TMP 1 a3 a2 weekNum yearMonth month year NumHolidays NumStrikeDays
sqlite3 $DB_TMP "delete from a3 where weekNum >= 260;"
exportCSVFile $DB_TMP a3 $OUTPUT_FOLDER/covariates/data3_week.csv
rm $DB_TMP



# data1: variables that vary at Canton and day/week level
copyTable2otherDB $DB_SCOPE      $DB_TMP Scope_CantonId_day
copyTable2otherDB $DB_HOLIDAYS   $DB_TMP SchoolVacations_CantonId_day
copyTable2otherDB $DB_GTRENDS    $DB_TMP GoogleTrends_CantonId_day
copyTable2otherDB $DB_CAR_COSTS  $DB_TMP AutomobileCosts_CantonId_day
copyTable2otherDB $DB_WEATHER    $DB_TMP Weather_CantonId_day
copyTable2otherDB $DB_SCOPE      $DB_TMP Scope_CantonId_week
copyTable2otherDB $DB_HOLIDAYS   $DB_TMP SchoolVacations_CantonId_week
copyTable2otherDB $DB_GTRENDS    $DB_TMP GoogleTrends_CantonId_week
copyTable2otherDB $DB_CAR_COSTS  $DB_TMP AutomobileCosts_CantonId_week
copyTable2otherDB $DB_WEATHER    $DB_TMP Weather_CantonId_week

equalJoin $DB_TMP 1 a1 Scope_CantonId_day SchoolVacations_CantonId_day   rowId
equalJoin $DB_TMP 1 a2 a1                 GoogleTrends_CantonId_day      rowId
equalJoin $DB_TMP 1 a3 a2                 AutomobileCosts_CantonId_day   rowId
equalJoin $DB_TMP 1 a4 a3                 Weather_CantonId_day           rowId
selectColumns $DB_TMP 1 a5 a4 CantonId dayNum NumVacationsDays NumDaysSchoolOff PRCP TAVG Assurance Entretien competitor_GoogleTrend
exportCSVFile $DB_TMP a5 $OUTPUT_FOLDER/covariates/data1_CantonId_day.csv
dropTables $DB_TMP a1 a2 a3 a4 a5

equalJoin $DB_TMP 1 a1 Scope_CantonId_week SchoolVacations_CantonId_week   rowId
equalJoin $DB_TMP 1 a2 a1                  GoogleTrends_CantonId_week      rowId
equalJoin $DB_TMP 1 a3 a2                  AutomobileCosts_CantonId_week   rowId
equalJoin $DB_TMP 1 a4 a3                 Weather_CantonId_week           rowId
selectColumns $DB_TMP 1 a5 a4 CantonId weekNum NumVacationsDays NumDaysSchoolOff PRCP TAVG Assurance Entretien competitor_GoogleTrend
sqlite3 $DB_TMP "delete from a5 where weekNum >= 260;"
exportCSVFile $DB_TMP a5 $OUTPUT_FOLDER/covariates/data1_CantonId_week.csv
dropTables $DB_TMP a1 a2 a3 a4 a5
rm $DB_TMP


### Clean up
rm $DB_SCOPE $DB_CENSUS $DB_TOURISM $DB_ELECTIONS $DB_PUBLIC_TRANSIT $DB_RENTAL_AGENCIES
rm $DB_RAIL_STRIKES $DB_HOLIDAYS $DB_GTRENDS $DB_CAR_COSTS $DB_WEATHER


cd ..
